#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/9/3 22:06
# @Author  : Jet
# @Email   : jyan@abot.cn
# @File    : my_mysql.py

import pymysql

# 定义数据库连接信息
db_setting = {
    "host": "localhost",
    "user": "www_abot_cn",
    "password": "www_abot_cn",
    "database": "www_abot_cn"
}

# 连接数据库
db_conn = pymysql.connect(**db_setting)

# 获取游标
db_cursor = db_conn.cursor()

# 组织SQL语句
sql001 = "select * from users001"

# 执行SQL语句
db_cursor.execute(sql001)

# 查看执行结果
user_list = db_cursor.fetchall()
print("\r\n没有执行插入新纪录前：")
for row001 in user_list:
    print(row001)

# 插入新记录
sql = "INSERT INTO `users001`(`name`, `password`) VALUES(%s, %s)"
new_value = ("程序员001", "dev_passw")

db_cursor.execute(sql, new_value)

# 新记录的主键值
new_userid = db_conn.insert_id()

db_conn.commit()

print("\r\n执行插入新纪录完成，新记录的ID：%d" % new_userid)

# ===== 再次执行SQL语句查询所有记录 ===
db_cursor.execute(sql001)
# 查看执行结果
user_list = db_cursor.fetchall()
print("\r\n执行插入新纪录之后：")
for row001 in user_list:
    print(row001)
# ============= End ================

# 将新记录的 name 字段的值修改为其他值
sql = "UPDATE `users001` set `name` = %s where userid = %s"
new_value = ("程序员003", new_userid)

row_num = db_cursor.execute(sql, new_value)
db_conn.commit()

print("\r\n修改纪录完成，受影响的行数：%d" % row_num)

# ===== 再次执行SQL语句查询所有记录 ===
db_cursor.execute(sql001)
# 查看执行结果
user_list = db_cursor.fetchall()
print("\r\n执行插入新纪录之后：")
for row001 in user_list:
    print(row001)
# ============= End ================

# 删除刚才新增的记录
sql = "DELETE from `users001` where userid = %s"
new_value = new_userid

row_num = db_cursor.execute(sql, new_value)
db_conn.commit()

print("\r\n删除纪录完成，共删除的记录数量：%d" % row_num)

# ===== 再次执行SQL语句查询所有记录 ===
db_cursor.execute(sql001)
# 查看执行结果
user_list = db_cursor.fetchall()
print("\r\n执行插入新纪录之后：")
for row001 in user_list:
    print(row001)
# ============= End ================

# 执行完成后关闭游标和数据库
db_cursor.close()
db_conn.close()